#!/usr/bin/env python

'''
    This program is used to build the SRC (Student Research Competition)
    spreadsheet.

    This program requires 4 spreadsheets from the SIS to run.  From conference
    reporting:
        1) "accept-gs.csv" -
            - Submission List
            - General Submission
            - Accepted SRC
            - Export to CSV, save as "accept-gs.csv"
        2) "accept-lb.csv" -
            - Submission List
            - Late Breaking
            - Accepted SRC
            - Export to CSV, save as "accept-lb.csv"
        3) "full-gs.csv" -
            - Reviewer By Person - Full LIst
            - General Submission
            - Export to CSV, save as "full-gs.csv"
        4) "full-lb.csv" -
            - Reviewer By Person - Full LIst
            - Late Breaking
            - Export to CSV, save as "full-lb.csv"

    The output columns are:
        rev1    - Reviewer 1's score
        rev2    - Reviewer 2's score
        ...
        revN    - Reviewer N's score
        id      - Submission ID
        title   - Submission Title
        concept - Average concept
        ...
'''

import sys, os, csv

# The SRC reviewers (as they appear in full-gs.csv)
Reviewers = {
    'Casas, Alexis'     : 'Alexis',
    'Miller, Erick S'   : 'Erick',
    'Gutierrez, Diego'  : 'Diego',
}

def error(msg):
    print '*'*10, msg
    sys.exit(1)

def stats(list):
    m = 0
    v = 0
    if len(list):
        for x in list:
            n = float(x)
            m += n
            v += n*n
        m /= float(len(list))   # Compute the mean
        v /= float(len(list))   # Compute mean of squares
        v = v - m*m             # Compute variance
    return [m, v]

class Submission:
    def __init__(self, id, title):
        self.Id = id
        self.Title = title
        self.Concept = []
        self.Novelty = []
        self.Interest = []
        self.Quality = []
        self.Summary = []
        self.Reviewers = {}

    def titleRow(self):
        row = []
        for r in Reviewers:
            row.append(Reviewers[r])
        row.append('Submission')
        row.append('Title')
        row.append('Reviews')
        for key in [ 'summary', 'concept', 'novelty', 'interest', 'quality']:
            row.append('Avg(%s)' % key)
            row.append('Var(%s)' % key)
        return row

    def makeRow(self):
        row = []
        for r in Reviewers:
            row.append(self.Reviewers.get(r, ''))
        row += [
            self.Id,
            self.Title,
        ]
        row.append(len(self.Summary))
        row += stats(self.Summary)
        row += stats(self.Concept)
        row += stats(self.Novelty)
        row += stats(self.Interest)
        row += stats(self.Quality)
        return row

Accepted = {}

def acceptedRow(row):
    '''
        0 - Submission
        1 - Status
        2 - Curated
        3 - Program
        4 - Presentation Type
        5 - Presentation Status
        6 - Title
        7 - Submitter
        8 - Email
        9 - Event
        10 - Session
    '''
    if row[0] == 'Submission' or row[0] == 'Completed:':
        return
    if Accepted.has_key(row[0]):
        error('Duplicate submission: %s' % row[0])
    Accepted[row[0]] = Submission(row[0], row[6])
    #print row[0], row[6]

def loadAccepted(sheet):
    fp = csv.reader(open(sheet, 'r'))
    for row in fp:
        acceptedRow(row)

def fullRow(row):
    '''
        0 - Reviewer ID
        1 - Reviewer
        2 - Role
        3 - Submission
        4 - Title
        5 - QuestionNumber
        6 - Question
        7 - Answer

    Questions:
        1 = Concept
        2 = Novelty
        3 = Interest
        4 = Quality
        5 = Discussion
        6 = Summary Recommendation
        7 = Confidence
        8 = Private
    '''
    id = row[3]
    sub = Accepted.get(id, None)
    if not sub:
        return
    rev = row[1]
    score = row[7]
    if score:
        q = row[5]
        if   q == '1':      sub.Concept.append( float(score) )
        elif q == '2':      sub.Novelty.append( float(score) )
        elif q == '3':      sub.Interest.append( float(score) )
        elif q == '4':      sub.Quality.append( float(score) )
        elif q == '6':
            sub.Summary.append( float(score) )
            if Reviewers.has_key(rev):
                sub.Reviewers[rev] = float(score)

def loadScores(sheet):
    fp = csv.reader(open(sheet, 'r'))
    for row in fp:
        fullRow(row)

def saveSummary(sheet):
    def cmpSubmission(s1, s2):
        if s1.Rank > s2.Rank: return -1
        if s1.Rank < s2.Rank: return  1
        return 0
    fp = csv.writer(open(sheet, 'w'))
    # Find the first accepted for the title row
    slist = []
    for id in Accepted:
        sub = Accepted[id]
        sub.Rank = stats(sub.Summary)[0]
        slist.append(sub)
        #print id, sub.Rank
    slist.sort(cmpSubmission)
    for sub in slist:
        fp.writerow(sub.titleRow())
        break
    for sub in slist:
        fp.writerow(sub.makeRow())

print 'Determining accepted SRC posters'
loadAccepted('accept-gs.csv')
loadAccepted('accept-lb.csv')
print 'Computing scores'
loadScores('full-gs.csv')
loadScores('full-lb.csv')

print 'Saving output.csv'
saveSummary('output.csv')
